use strict ; use warnings ; use Exporter;
use Configurator ; use Logger ; use FileHandler ; 
use Spreadsheet::ParseExcel;
use File::Find ; use Cwd ; 


package ExcelToMSSqlInsert; 

#	anonymous hash !!!
our ( $confHolder , $objLogger , $MyBareName )= () ; 

my $VERSION = '0.5.0';

my ( $SqlInstallDir, $LogLevel , $TokenDelimiterAsciiNumber ) = () ; 
my ( $RowEnd , $FileExcelInput , $VersionDir ) = () ; 

sub  main {

	Initialize();
	
	$objLogger->LogInfoMsg	(	" START MAIN " ) ; 	
	ParseExcel ( $FileExcelInput  , $TokenDelimiterAsciiNumber   ) ; 
	$objLogger->LogInfoMsg	(	" STOP MAIN \n\n" ) ; 

} #eof sub main 




sub ParseExcel {

my $FileExcelInput = shift ; 
my $TokenDelimiterAsciiNumber = shift ; 
my $SqlInstallDir = shift ; 

 $RowEnd  = 	$confHolder>{'RowEnd'} ; 

my $strToReturn = "" ; 

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->Parse("$FileExcelInput");
my $NewSqlFilesInstallDir = $confHolder -> {'NewSqlFilesInstallDir'} ; 
my $Data = () ;

	foreach my $worksheet (@{$workbook->{Worksheet}}) {

	$objLogger->LogDebugMsg	 ( "foreeach my worksheet " ); 
	my $WorkSheetName = $worksheet->{'Name'} ; 
	$objLogger->LogDebugMsg	("\$WorkSheetName is $WorkSheetName" );
	 

	my @filesToRun = @{GetSqlInsertFiles()} ; 

	my $FileToPrint = () ; 

	my $flagFileExists = ()  ; 
	foreach my $FileToPrintMatch ( @filesToRun ) 
	{
		#$objLogger->LogDebugMsg	( "\$FileToPrintMatch is $FileToPrintMatch " );
		my $filePattern= 'TableInsert\.sql' ; 

		if ( $FileToPrintMatch =~m/^.*$WorkSheetName\.$filePattern$/ )
			{
				$FileToPrint = $FileToPrintMatch ; 
				$flagFileExists = 1  ; 
				last ; 
			}
	} #eof foreach 

	# todo:if flag found == 0 
	# create the file if does not exist ... and plalce it in the SqlInstallDir/DbName
	$FileToPrint = "$NewSqlFilesInstallDir/$WorkSheetName\.TableInsert.sql"  unless $flagFileExists  ; 

	$objLogger->LogDebugMsg	 (  "\$FileToPrint is $FileToPrint "  ) ; 

	my $RowMin = $worksheet->{MinRow} ; 
	my $RowMax= $worksheet->{MaxRow} ; 
	

	#    my ( $RowMin, $RowMax) = $worksheet->row_range();
	#    my ( $MinCol, $MaxCold ) = $worksheet->col_range();

			for my $row ( ($RowMin ) .. $RowMax) {


			my $MinCol = $worksheet->{MinCol} ; 

			my $MaxCol = $worksheet->{MaxCol} ; 

			$Data .= ") \n SELECT " if ( $row == 1) ;    
			$Data .= "\n UNION ALL SELECT " if ( $row >1) ; 

			# The irst row of the Excel conttains t he meta data , the rest the data
			my $tokenStart = (); 
			$tokenStart = '\''  if ( $row>0) ; 
			$tokenStart = '['  if ( $row == 0) ; 

			my $tokenStop = () ; 
			$tokenStop = '\''  if ( $row>0) ; 
			$tokenStop = ']'  if ( $row == 0) ; 


			 for my $col ( ($MinCol ) .. $MaxCol ) {

				my $cell = $worksheet->{Cells}[$row][$col]  ; 
				my $token = '' ; 
				unless ( $cell ) 
				{
					$strToReturn .=  ' NULL ,' ;
					next  ; 
				}


				$objLogger->LogDebugMsg	( " Row, Col    = ($row, $col)" ) ; 
				$objLogger->LogDebugMsg	 ( " cell value = ", $cell->Value() ); 

				my $cellValueToReplace = $worksheet->{Cells}[$RowMin][$col]  ; 
				$objLogger->LogDebugMsg	 ( "\$cellValueToReplace is $cellValueToReplace " );


				my $StrValueToReplaceWith = $cell->Value() ;
				$objLogger->LogDebugMsg	 (  " \$StrValueToReplaceWith  is $StrValueToReplaceWith  " ) ;
				$token = $cell->Value() ; 
				$token =~ s/\'/\'\'/g ; 	#replace ' with '' to escape sql


					#NULL is a key word should not be tokenized in MSSQL sql
					if ( trim($token) eq 'NULL')		{
						$Data .= $token  .  ' , '; 
					}
					 else 		{
						$Data.= $tokenStart . $token  . $tokenStop . ' , ' ; 
					}
							#debug print "\$strToReturn is $strToReturn \n" ; 
							#$strToReturn .=  $cell->Value() . $TokenDelimiterAsciiNumber       ;				#The Value
							#debug print "Unformatted = ", $cell->Unformatted(), "\n";

					} #eof column

				chop ($Data) ; #remove the latest delimiter
				chop ($Data) ; #remove the latest delimiter

				$Data .= $RowEnd ; 	#end the row    
				$objLogger->LogDebugMsg	 (  "\$FileToPrint  is $FileToPrint " ) ; 


			} #eof foreach row

			my $objFileHandler = new FileHandler (); 
			$strToReturn = $objFileHandler-> ReadFileReturnString(
																																$confHolder->{'InsertTemplateFile'} );
			
			$strToReturn = eval  "$strToReturn"   or 
					$objLogger->LogMsg(" Check your template file syntax in perl  $!" ) ; 
			
			$objLogger->LogDebugMsg	 (  "\$strToReturn  is $strToReturn " ) ; 
			$objLogger->LogInfoMsg	 (  "START PRINTING TO $FileToPrint " ) ; 
			$objFileHandler -> PrintToFile ( $FileToPrint , $strToReturn ) ; 
			# Action !!!
			RunSqlInsertFromFile ( $FileToPrint ) ; 
			
			
			$strToReturn = '' ; 
			$Data = '' ; 
	} #eof worksheet

} #eof sub 



sub trim	
{
    $_[0]=~s/^\s+//;
    $_[0]=~s/\s+$//;
    return $_[0];
}



sub GetSqlInsertFiles
{

		my $objFileHandler = new FileHandler () ; 
		$SqlInstallDir = $objFileHandler->ToUnixDir( $SqlInstallDir )  ; 
		
		$objLogger->LogDebugMsg	("sub GetSqlInsertFiles-- \$SqlInstallDir is $SqlInstallDir" ) ; 		
		
		my @filesToRun = ();
		my $filePattern = '\.TableInsert\.sql' ; 
		#add only files of type filePattern recursively from the $SqlInstallDir
		
		
		File::Find::find( sub { push @filesToRun, $File::Find::name  
										if ( m/^(.*)$filePattern$/ ) }, $SqlInstallDir) ;

		foreach my $file ( @filesToRun  ) 	{
			$objLogger->LogDebugMsg	( "GetSqlInsertFiles \$file is $file\n" );
		} #eof foreach  
		
		
	return \@filesToRun ; 
} #eof sub


#
sub RunSqlInsertFromFile {

my $FileToRun = shift ; 
my $objFileHandler = new FileHandler () ; 
$FileToRun = $objFileHandler->ToWindowsDir ( $FileToRun ) ; 

my $SqlUser= $confHolder-> {'SqlUser'} ; 
my $SqlPass= $confHolder-> {'SqlPass'} ; 
my $HostNameAndInstance = $confHolder-> {'HostNameAndInstance'} ; 
my $DataBaseName = $confHolder-> {'DataBaseName'} ; 
my $TimeOut = $confHolder-> {'TimeOut'} ; 

my $cmd = "SQLCMD -U $SqlUser -P $SqlPass -S $HostNameAndInstance -d $DataBaseName -t $TimeOut -w 80 -u -p 1 -b -i $FileToRun -r1" ; 

$objLogger-> LogInfoMsg ( "RUNNING SQL FILE: $FileToRun " ) ; 
$objLogger-> LogInfoMsg ( "$cmd" ) ; 
my $cmdOutput = `$cmd` ; 
$objLogger-> LogInfoMsg ( "$cmdOutput" ) ; 

#SQLCMD -U %_sqlLogin% -P %_passwd% -S %_hostNameAndInstance% -d %_DbName%  -t 30000 -w 80 -u -p 1 -b -i "%%i" -r1 


} #eof sub


sub Initialize {

		# strip the remote path and keep the bare name
		$0=~m/^(.*)(\\|\/)(.*)\.([a-z]*)/; 
		$MyBareName = $3; 
		my $RunDir= $1 ; 

		# create the configurator object 
		my $objConfigurator = new Configurator($RunDir , $MyBareName ); 
		# get the hash having the vars 
		$confHolder = $objConfigurator ->getConfHolder () ; 
		# pring the hash vars 
		print $objConfigurator->dumpIni();	
		$objLogger = new Logger (\$confHolder) ; 

		$LogLevel = 																	$confHolder->{'LogLevel'} || 4 ; 
		$TokenDelimiterAsciiNumber = 	chr(	$confHolder->{'TokenDelimiterAsciiNumber'}) || chr(44) ;
		$RowEnd = 																	chr(	$confHolder->{'RowEnd'}) || chr(12) ;
		$FileExcelInput = 													$confHolder->{'FileExcelInput'} ; 
		$SqlInstallDir = 														$confHolder->{'SqlInstallDir'} ; 

		$objLogger->LogDebugMsg	 ( "\$FileExcelInput is $FileExcelInput" ) ; 
		$objLogger->LogDebugMsg	(	"using the following settings" . $objConfigurator->dumpIni	() ) ; 


} #eof sub



# =========================================== eof sub trim 
# Action !!!
main();


1 ; 

__END__

=head1 NAME

ExcelToSqlInsert - 

=head1 DESCRIPTION

The main script scans recursively a predifined directory uses an excel file for input
takes the sheet name as a table name and generates sql inserts using the first column 
it finds the file to write the sql inserts by search for WorkSheetName.TableInsert.sql pattern 
in a predifned SqlInstallDir, where the sql install files are situated , if a TableName.TableInsert does not exist it creates it in a preconfigured  director. After each sql insert file is generated it is ran against a predified database and the output stored into a log file

=head1 README

for how-to use this script check the ReadMe.txt 

=head1 PREREQUISITES

SpreadSheet::ParseExcel
Exporter
FileHandler
The sql Server 2008 client

=head1 COREQUISITES


=pod OSNAMES

any ( Windows 7 only tested ) 

=pod SCRIPT CATEGORIES

configurations 
=cut
